package com.zhaokuner.ctrl;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.function.Consumer;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class TestExcel {

	public static void main(String[] args) throws Exception {
		TestExcel te = new TestExcel();
		te.setInputPath("/Users/zhaokuner/Desktop/a.xlsx");
		te.putConsumer(1, (c) -> {
			String value = TestExcel.getCellValue(c);
			if (value.isEmpty()) {
				return;
			}
			c.setCellType(CellType.STRING);
			c.setCellValue(value);
		});
		te.deal();
	}

	private Workbook workbook;

	/*
	 * 由于Excel当中的单元格Cell存在类型,若获取类型错误 就会产生错误, 所以通过此方法将Cell内容全部转换为String类型
	 */
	public static String getCellValue(Cell cell) {
		if (null == cell) {
			return "";
		}
		String str = null;
		switch (cell.getCellTypeEnum()) {
		case BLANK:
			str = "";
			break;
		case BOOLEAN:
			str = String.valueOf(cell.getBooleanCellValue());
			break;
		case FORMULA:
			str = String.valueOf(cell.getCellFormula());
			break;
		case NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				// 如果是date类型则 ，获取该cell的date值
				str = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(DateUtil.getJavaDate(cell.getNumericCellValue()));
			} else { // 纯数字
				str = String.valueOf(cell.getNumericCellValue());
			}
			break;
		case STRING:
			str = String.valueOf(cell.getStringCellValue());
			break;
		default:
			str = "";
			break;
		}
		return str;
	}

	private String inputPath = "";
	private String outputPath = "";
	private int sheetAt = 0;

	HashMap<Integer, Consumer<Cell>> map = new HashMap<>();

	public void putConsumer(Integer row, Consumer<Cell> c) {
		map.put(row, c);
	}

	@Before
	public void setUp() throws InvalidFormatException, IOException {
		if (null == outputPath || outputPath.isEmpty()) {
			outputPath = inputPath;
		}
		// 加载excel文件,自动判断是HSSF还是XSSF
		workbook = WorkbookFactory.create(new File(inputPath));
	}

	public void deal() throws Exception {
		if (null == inputPath || inputPath.isEmpty()) {
			System.out.println("inputPath is null");
		}
		setUp();
		testReadExcel();
		testWriteExcel();
	}

	/*
	 * 读取一个已存在的Excel
	 */
	@Test
	public void testReadExcel() throws InvalidFormatException, IOException {

		// 获取第一个工作目录,下标从0开始
		Sheet sheet = workbook.getSheetAt(sheetAt);

		// 获取该工作目录最后一行的行数
		int lastRowNum = sheet.getLastRowNum();

		for (int i = 0; i <= lastRowNum; i++) {

			// 获取下标为i的行
			Row row = sheet.getRow(i);

			// 获取该行单元格个数
			int lastCellNum = row.getLastCellNum();

			for (int j = 0; j < lastCellNum; j++) {

				// 获取下标为j的单元格
				Cell cell = row.getCell(j);

				Consumer<Cell> p = map.get(j + 1);
				if (null != p) {
					p.accept(cell);
				}
			}
		}
	}

	/*
	 * 创建简单的Excel
	 */
	@After
	public void testWriteExcel() throws IOException {
		Path p = Paths.get(outputPath);
		FileOutputStream fos = new FileOutputStream(p.getParent().toString() + "/" + new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date()) + "_" + p.getFileName());
		workbook.write(fos);
	}

	public String getInputPath() {
		return inputPath;
	}

	public void setInputPath(String inputPath) {
		this.inputPath = inputPath;
	}

	public String getOutputPath() {
		return outputPath;
	}

	public void setOutputPath(String outputPath) {
		this.outputPath = outputPath;
	}

	public int getSheetAt() {
		return sheetAt;
	}

	public void setSheetAt(int sheetAt) {
		this.sheetAt = sheetAt;
	}

	public HashMap<Integer, Consumer<Cell>> getMap() {
		return map;
	}

	public void setMap(HashMap<Integer, Consumer<Cell>> map) {
		this.map = map;
	}
}
